<?
session_start();
include('conn.php');
//$qry1 = "SELECT * from invoicemaster"; //where status=9

//$qry1 = "SELECT A.cusid FROM invoicemaster A, customermast D where A.status = 0 and A.custid=D.custid ;

$qry1 = "SELECT c.custid AS custid, t.invoicenumber AS invoicenumber, DATE_FORMAT(t.invoicedate,'%d-%m-%Y') AS invoicedate, c.custdesc AS custdesc, c.email AS custemail,  sum( t.creditamt ) AS invoiceamt, '' AS DueOn, '' AS balance, '' AS invoicefile, '' AS
STATUS
FROM transactionmast t
LEFT JOIN customermast c ON t.custid = c.custid
WHERE t.bid = 15 and t.invoicenumber != ''
AND t.invoicedate >= '2014-01-01'
and c.custid in(
SELECT c.custid
FROM customermast c
LEFT JOIN transactionmast t ON c.custid = t.custid
where t.status =0
GROUP BY c.custid, c.custdesc, c.custtype, t.status
)
GROUP BY c.custid, t.invoicenumber, t.invoicedate
ORDER BY  t.invoicenumber DESC";

$qry = mysql_query($qry1);
$data = array();
$i=1;
while($rows = mysql_fetch_array($qry))
{
    $data[] = array("invoicenumber" => $rows['invoicenumber'],
    			    "custid" => $rows['custid'],
                    "custdesc" => $rows['custdesc'],
					"custemail" => $rows['custemail'],
				    "invoicedate" => $rows['invoicedate'],
				    "invoiceamt"=> $rows['invoiceamt'],
				    "DueOn"=> $rows['DueOn'],
				    "balance"=> $rows['balance'],
                    "invoicefile"=> $rows['invoicefile'],
				    "status"=> $rows['status'] );
}
echo json_encode($data);
?>